#! /usr/bin/env python
#coding=utf-8

##
# xls 配置导表工具
# @author:zhengjianhua

# 主要功能：
#     1 根据excel 自动生成配置的PB定义
#     2 生成PB的序列化后的二进制数据或者文本数据
#

# 依赖:
# 1 protobuf
# 2 xlrd
##


from logging import logThreads
import xlrd # for read excel
import sys
import os
import traceback

# TAP的空格数
TAP_BLANK_NUM = 4

# 这一行还表示重复的最大个数，或结构体元素数
FIELD_TYPE_ROW = 0
FIELD_NAME_ROW = 1
FIELD_COMMENT_ROW = 2
#日志辅助类
class LogHelp :
    _logger = None
    _close_imme = True

    @staticmethod
    def set_close_flag(flag):
        LogHelp._close_imme = flag

    @staticmethod
    def _initlog():
        import logging

        LogHelp._logger = logging.getLogger()
        logfile = 'xls2protobuf_log.log'
        hdlr = logging.FileHandler(logfile)
        formatter = logging.Formatter('%(asctime)s|%(levelname)s|%(lineno)d|%(funcName)s|%(message)s')
        hdlr.setFormatter(formatter)
        LogHelp._logger.addHandler(hdlr)
        LogHelp._logger.setLevel(logging.NOTSET)
        # LogHelp._logger.setLevel(logging.WARNING)

        LogHelp._logger.info("\n\n\n")
        LogHelp._logger.info("logger is inited!")

    @staticmethod
    def get_logger() :
        if LogHelp._logger is None :
            LogHelp._initlog()

        return LogHelp._logger

    @staticmethod
    def close() :
        if LogHelp._close_imme:
            import logging
            if LogHelp._logger is None :
                return
            logging.shutdown()

# log macro
LOG_DEBUG=LogHelp.get_logger().debug
LOG_INFO=LogHelp.get_logger().info
LOG_WARN=LogHelp.get_logger().warn
LOG_ERROR=LogHelp.get_logger().error


class SheetInterpreter:
    #通过excel配置生成配置的protobuf定义文件
    def __init__(self, xls_file_path):
        self._xls_file_path = xls_file_path
        self._config_name = xls_file_path.split('/')[-1].replace('.xls', '').replace('.xlsx', '')

        try :
            self._workbook = xlrd.open_workbook(self._xls_file_path)
        except:
            print("open xls file(%s) failed!"%(self._xls_file_path))
            raise

        try :
            self._sheet =self._workbook.sheet_by_index(0)
        except:
            print("open sheet failed!")

        # 行数和列数
        self._row_count = len(self._sheet.col_values(0))
        self._col_count = len(self._sheet.row_values(0))

        self._row = 0
        self._col = 0

        # 将所有的输出先写到一个list， 最后统一写到文件
        self._output = []
        # 排版缩进空格数
        self._indentation = 0
        # field number 结构嵌套时使用列表
        # 新增一个结构，行增一个元素，结构定义完成后弹出
        self._field_index_list = [1]
        # 当前行是否输出，避免相同结构重复定义
        self._is_layout = True
        # 保存所有结构的名字
        self._struct_name_list = []

        self._pb_file_name = self._config_name + ".proto"


    def Interpreter(self) :
        #对外的接口
        LOG_INFO("begin Interpreter, row_count = %d, col_count = %d", self._row_count, self._col_count)

        self._LayoutFileHeader()
        self._output.append("syntax = \"proto3\";\n")
        self._output.append("package protoconfig;\n")

        self._LayoutStructHead(self._config_name)
        self._IncreaseIndentation()

        while self._col < self._col_count :
            self._FieldDefine(0)

        self._DecreaseIndentation()
        self._LayoutStructTail()

        self._LayoutArray()

        self._Write2File()

        LogHelp.close()
        # 生成解析代码
        try :
            command = "protoc --python_out=./ " + self._pb_file_name
            os.system(command)
            if parser_language != None:
                command = "\nprotoc --%s_out=./ "%(parser_language) + self._pb_file_name
                os.system(command)
        except:
            print("protoc failed!")
            raise

    def _FieldDefine(self, repeated_num) :
        LOG_INFO("row=%d, col=%d, repeated_num=%d", self._row, self._col, repeated_num)
        field_type = str(self._sheet.cell_value(FIELD_TYPE_ROW, self._col)).strip()
        #5|3 结构体 重复5次 3个字段
        if field_type.find('|') != -1:
            repeated_and_fieldnum = field_type.split('|')
            repeated_num = int(repeated_and_fieldnum[0])
            field_num = int(repeated_and_fieldnum[1])
            field_name = str(self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)).strip()
            struct_name = str(self._sheet.cell_value(FIELD_NAME_ROW, self._col)).strip()

            if (self._IsStructDefined(struct_name)) :
                self._is_layout = False
            else :
                self._struct_name_list.append(struct_name)
                self._is_layout = True

            self._StructDefine(struct_name, field_num)

            self._is_layout = True
            
            self._LayoutOneField("repeated", struct_name, field_name)
            self._col += (repeated_num-1) * field_num

        elif field_type.isdigit() or field_type.find('.') != -1:
            field_num = int(float(field_type))
            field_name = str(self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)).strip();
            struct_name = str(self._sheet.cell_value(FIELD_NAME_ROW, self._col)).strip()
            if (self._IsStructDefined(struct_name)) :
                    self._is_layout = False
            else :
                self._struct_name_list.append(struct_name)
                self._is_layout = True

            self._StructDefine(struct_name, field_num)
            self._is_layout = True
            self._LayoutOneField('', struct_name, field_name)

        elif field_type.find("repeated") != -1:
            rule_and_type = field_type.split('_')
            field_rule = "repeated"
            field_type = rule_and_type[1]
            field_name = self._sheet.cell_value(FIELD_NAME_ROW, self._col).strip()
            field_comment = self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)
            #comment = field_comment.encode("utf-8")
            self._LayoutComment(field_comment)
            self._LayoutOneField(field_rule, field_type, field_name)
            self._col += 1

        elif len(field_type) == 0:
            self._col += 1

        else:
            field_name = self._sheet.cell_value(FIELD_NAME_ROW, self._col).strip()
            field_comment = self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)
            #comment = field_comment.encode("utf-8")
            self._LayoutComment(field_comment)
            self._LayoutOneField('', field_type, field_name)
            self._col += 1

    def _IsStructDefined(self, struct_name) :
        for name in self._struct_name_list :
            if name == struct_name :
                return True
        return False

    def _StructDefine(self, struct_name, field_num) :
        #嵌套结构定义

        self._col += 1
        self._LayoutStructHead(struct_name)
        self._IncreaseIndentation()
        self._field_index_list.append(1)

        while field_num > 0 :
            self._FieldDefine(0)
            field_num -= 1

        self._field_index_list.pop()
        self._DecreaseIndentation()
        self._LayoutStructTail()
    #生成PB文件的描述信息
    def _LayoutFileHeader(self) :
        self._output.append("/**\n")
        self._output.append("* 此文件是通过工具自动生成的，不要手动修改\n")
        self._output.append("*/\n")
        self._output.append("\n")

    #生成结构头
    def _LayoutStructHead(self, struct_name) :
        
        if not self._is_layout :
            return
        self._output.append("\n")
        self._output.append(" "*self._indentation + "message " + struct_name + "{\n")
    #生成结构尾
    def _LayoutStructTail(self) :
        
        if not self._is_layout :
            return
        self._output.append(" "*self._indentation + "}\n")
        self._output.append("\n")
    # 改用C风格的注释，防止会有分行
    def _LayoutComment(self, comment) :
        if not self._is_layout :
            return
        if comment.count("\n") > 1 :
            if comment[-1] != '\n':
                comment = comment + "\n"
                comment = comment.replace("\n", "\n" + " " * (self._indentation + TAP_BLANK_NUM),
                        comment.count("\n")-1 )
                self._output.append(" "*self._indentation + "/** " + comment + " "*self._indentation + "*/\n")
        else :
            self._output.append(" "*self._indentation + "/** " + comment + " */\n")
    #输出一行定义
    def _LayoutOneField(self, field_rule, field_type, field_name) :
        if not self._is_layout :
            return
      
        if len(field_rule) != 0:
            field_rule = field_rule + " "
        self._output.append(" "*self._indentation + field_rule + field_type \
                    + " " + field_name + " = " + self._GetAndAddFieldIndex() + ";\n")
    #增加缩进
    def _IncreaseIndentation(self) :
        self._indentation += TAP_BLANK_NUM
    #减少缩进
    def _DecreaseIndentation(self) :
        self._indentation -= TAP_BLANK_NUM
    #获得字段的序号, 并将序号增加
    def _GetAndAddFieldIndex(self) :
        index = str(self._field_index_list[- 1])
        self._field_index_list[-1] += 1
        return index
    #输出数组定义
    def _LayoutArray(self) :
        self._output.append("message " + self._config_name + "_list {\n")
        self._output.append("    repeated " + self._config_name + " items = 1;\n}\n")
    #输出到文件
    def _Write2File(self) :
        pb_file = open(self._pb_file_name, "w+", encoding="utf-8")
        pb_file.writelines(self._output)
        pb_file.close()

#解析excel的数据
class DataParser:
    def __init__(self, xls_file_path):
        self._xls_file_path = xls_file_path
        self._config_name = xls_file_path.split('/')[-1].replace('.xls', '').replace('.xlsx', '')

        try :
            self._workbook = xlrd.open_workbook(self._xls_file_path)
        except:
            print("open xls file(%s) failed!"%(self._xls_file_path))
            raise

        try :
            self._sheet =self._workbook.sheet_by_index(0)
        except:
            print ("open sheet failed!")
            raise

        self._row_count = len(self._sheet.col_values(0))
        self._col_count = len(self._sheet.row_values(0))

        self._row = 0
        self._col = 0

        try:
            self._module_name = self._config_name + "_pb2"
            sys.path.append(os.getcwd())
            exec('from '+self._module_name + ' import *')
            self._module = sys.modules[self._module_name]
        except:
            print("load module(%s) failed"%(self._module_name))
            raise
    #对外的接口:解析数据
    def Parse(self) :
        LOG_INFO("begin parse, row_count = %d, col_count = %d", self._row_count, self._col_count)

        item_array = getattr(self._module, self._config_name+'_list')()

        # 先找到定义ID的列
        id_col = 0
        for id_col in range(0, self._col_count) :
            info_id = str(self._sheet.cell_value(self._row, id_col)).strip()
            if info_id == "" :
                continue
            else :
                break

        for self._row in range(3, self._row_count) :
            # 如果 id 是 空 直接跳过改行
            info_id = str(self._sheet.cell_value(self._row, id_col)).strip()
            if info_id == "" :
                #LOG_WARN("%d is None", self._row)
                continue
            item = item_array.items.add()
            self._ParseLine(item)
        self._WriteReadableData2File(str(item_array).encode("utf-8"))
        data = item_array.SerializeToString()
        self._WriteData2File(data)
        LogHelp.close()

    def _ParseLine(self, item) :
        LOG_INFO("%d", self._row)

        self._col = 0
        while self._col < self._col_count :
            self._ParseField(item)

    def _ParseField(self, item) :
        field_type = str(self._sheet.cell_value(FIELD_TYPE_ROW, self._col)).strip()
        if field_type.find('|') != -1:
            repeated_and_fieldnum = field_type.split('|')
            repeated_num = int(repeated_and_fieldnum[0])
            field_num = int(repeated_and_fieldnum[1])
            field_name = str(self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)).strip()
            repeated_struct_item = item.__getattribute__(field_name)
            #跳过定义列
            self._col += 1
            for num in range(0, repeated_num):
                field = repeated_struct_item.add()
                self._ParseStruct(field_num, field)
                if field == None:
                    print("-------")
                    repeated_struct_item.pop()
        
        elif field_type.isdigit() or field_type.find('.') != -1:
            field_num = int(float(field_type))
            field_name = str(self._sheet.cell_value(FIELD_COMMENT_ROW, self._col)).strip();
            struct_item = item.__getattribute__(field_name)
            #跳过定义列
            self._col += 1
            self._ParseStruct(field_num, struct_item)

        elif field_type.find("repeated") != -1:
            rule_and_type = field_type.split('_')
            field_type = rule_and_type[1]
            field_value = str(self._sheet.cell_value(self._row, self._col))
            field_values = field_value.split(';')
            field_name = str(self._sheet.cell_value(FIELD_NAME_ROW, self._col)).strip()
            repeat_items = item.__getattribute__(field_name)
            for value in field_values:
                repeat_item = self._ParFieldValue(field_type, value)
                if repeat_item != None:
                    repeat_items.append(repeat_item)
            self._col += 1

        elif len(field_type) == 0:
            self._col += 1

        else:
            field_name = str(self._sheet.cell_value(FIELD_NAME_ROW, self._col)).strip()
            field_value = self._GetFieldValue(field_type, self._row, self._col)
            if field_value != None:
                item.__setattr__(field_name, field_value)
            self._col += 1

    #嵌套结构数据读取
    def _ParseStruct(self, field_num, struct_item) :
        while field_num > 0 :
            self._ParseField(struct_item)
            field_num -= 1
    #根据类型将pb类型转换为python类型
    def _ParFieldValue(self, field_type, field_value):
        try:
            if field_type == "int32" or field_type == "int64"\
                    or  field_type == "uint32" or field_type == "uint64"\
                    or field_type == "sint32" or field_type == "sint64"\
                    or field_type == "fixed32" or field_type == "fixed64"\
                    or field_type == "sfixed32" or field_type == "sfixed64" :
                        if len(str(field_value).strip()) <=0 :
                            return None
                        else :
                            return int(float(field_value))
            elif field_type == "double" or field_type == "float" :
                    if len(str(field_value).strip()) <=0 :
                        return None
                    else :
                        return float(field_value)
            elif field_type == "string" :
                field_value = str(field_value)
                if len(field_value) <= 0 :
                    return None
                else :
                    return field_value
            elif field_type == "bytes" :
                field_value = str(field_value).encode("utf-8")
                if len(field_value) <= 0 :
                    return None
                else :
                    return field_value
            else :
                return None
        except:
            print("parse cell(%s, %s) error, please check it, maybe type is wrong."%(field_type, field_value))
            raise
    #将pb类型转换为python类型
    def _GetFieldValue(self, field_type, row, col) :
        field_value = self._sheet.cell_value(row, col)
        #LOG_INFO("%d|%d|%s", row, col, field_value)
        return self._ParFieldValue(field_type, field_value)

    def _WriteData2File(self, data) :
        file_name = self._config_name + ".bytes"
        file = open(file_name, 'wb+')
        file.write(data)
        file.close()

    def _WriteReadableData2File(self, data) :
        file_name = self._config_name + ".txt"
        file = open(file_name, 'wb+')
        file.write(data)
        file.close()


#入口
if __name__ == '__main__' :
    if len(sys.argv) < 2 :
        print("Usage: %s  xls_file" %(sys.argv[0]))
        print('help\n'\
            'xlsfile(require) parser_language(optional csharp or java..)\n'\
        )
        sys.exit(-1)

    xls_file_path =  sys.argv[1]
    xls_file_path = xls_file_path.replace('\\','/')
    parser_language = None
    if len(sys.argv) > 2:
        parser_language = sys.argv[2]
    try :
        tool = SheetInterpreter(xls_file_path)
        tool.Interpreter()
    except BaseException as e :
        print("Interpreter Failed!!!")
        traceback.print_exc()
        sys.exit(-3)
    print("Interpreter Success!!!")

    try :
        parser = DataParser(xls_file_path)
        parser.Parse()
    except BaseException as e :
        print("Parse Failed!!!")
        traceback.print_exc()
        sys.exit(-4)

    print("Parse Success!!!")

